package volleyball.control;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import volleyball.model.Game;
import volleyball.model.Player;

/**
 * Uploads Game objects to a database.
 * @author Joshua Kulhavy-Sutherland
 *
 */
public class DataBaseUploader {
	private final String DB_VOLLEYBALL = "volleyball";
	//tables
	private final String TABLE_GAME_INFO    = "game_info";
	private final String TABLE_GAME_SUMMARY = "game_summary";
	//game_info table columns
	private final String COL_DESCRIPTION = "gameDescription";
	private final String COL_DATE        = "date";
	private final String COL_ROUND       = "round";
	private final String COL_TYPE        = "gameType";
	//game_summary table columns
	private final String COL_PLAYER_NAME  = "playerName";
	private final String COL_SERVE        = "serve";
	private final String COL_SERVE_ERR  = "serveError";
	private final String COL_SERVE_ACE    = "serveAce";
	private final String COL_SET          = "set";
	private final String COL_ASSIST       = "assist";
	private final String COL_BHE          = "ballHandlingError";
	private final String COL_PASS0        = "pass0";
	private final String COL_PASS1        = "pass1";
	private final String COL_PASS2        = "pass2";
	private final String COL_PASS3        = "pass3";
	private final String COL_ATK_KILL     = "attackKill";
	private final String COL_ATK_ERR      = "attackError";
	private final String COL_ATK_ATT      = "attackAttempt";
	private final String COL_DIG          = "dig";
	private final String COL_BLOCK        = "block";
	private final String COL_BLOCK_ERR    = "blockError";
	private final String COL_BLOCK_ATT    = "blockAttempt";
 	//shared columns
	private final String COL_GAME_ID     = "gameID";
	
	private final String DB_CLASS = "com.mysql.jdbc.Driver";
	
	private final String PATH_PREFIX = "jdbc:mysql://";
	
	private int gameID;
	private Connection con;
	private Statement stmt;
	private String dbPath;
	private String dbUser;
	private String dbPW;
	
	/**
	 * Creates a DataBaseUploader ready to establish a connection with the provided database.
	 * @param dbPath The path to use for uploading to the database
	 * @param dbUser The username that will be used for uploading to the database
	 * @param dbPassword The password that will be used for uploading to the database
	 */
	public DataBaseUploader(String dbPath, String dbUser, String dbPassword){
		this.dbPath = dbPath;
		this.dbUser = dbUser;
		this.dbPW = dbPassword;
	}
	
	/**
	 * Uploads the Game to the database that was passed through the constructor. 
	 * @param game A Game to upload to the database
	 * @return true if the game uploads successfully, otherwise false
	 */
	public boolean upload(Game game){
		try{
			con = DriverManager.getConnection(PATH_PREFIX+dbPath,dbUser,dbPW);
			stmt = con.createStatement();
			uploadGame(game);
			uploadStatistics(game);
			con.close();
			return true;
		} catch (SQLException sql){
			sql.printStackTrace();
			return false;
		}
	}
	
	/**
	 * Uploads the Game fields of the Game object, but not the players.
	 * @param statistics The Game to upload
	 */
	private void uploadGame(Game statistics){
		String update = "INSERT INTO `"+DB_VOLLEYBALL+"`.`"+TABLE_GAME_INFO+"` (`"+COL_GAME_ID+"`, `"+COL_DESCRIPTION+"`, " +
				"`"+COL_DATE+"`, `"+COL_ROUND+"`, `"+COL_TYPE+"`) VALUES " +
						"(NULL, '"+statistics.getDescription()+"', '"+statistics.getDate()+"', '"+statistics.getRound()+
						"', '"+statistics.getGameType()+"')";
		runUpdate(update);
		setGameID(statistics);
	}
	
	/**
	 * Uploads the Players in the Game.
	 * @param statistics The Game holding the Players
	 */
	private void uploadStatistics(Game statistics){
		for (int x = 0; x < 4; x++){
			runUpdate(generateGameSummaryUpdate(statistics.getPlayer(x)));
		}
	}
	
	/**
	 * Generates a large query that inserts all of the info contained in Player into the database
	 * @param player The Player that is being inserted
	 * @return A query that will insert the Players info into the database
	 */
	private String generateGameSummaryUpdate(Player player){
		String query = "INSERT INTO `"+DB_VOLLEYBALL+"`.`"+TABLE_GAME_SUMMARY+"` (`"+COL_PLAYER_NAME+"`, `"+COL_GAME_ID+
				"`, `"+COL_SERVE+"`, `"+COL_SERVE_ERR+"`, `"+COL_SERVE_ACE+"`, `"+COL_SET+"`, `"+COL_ASSIST+
				"`, `"+COL_BHE+"`, `"+COL_PASS0+"`, `"+COL_PASS1+"`, `"+COL_PASS2+"`, `"+COL_PASS3+"`, `"+COL_ATK_KILL+
				"`, `"+COL_ATK_ERR+"`, `"+COL_ATK_ATT+"`, `"+COL_DIG+"`, `"+COL_BLOCK+"`, `"+COL_BLOCK_ERR+
				"`, `"+COL_BLOCK_ATT+"`) VALUES ('"+player.getName()+"', '"+gameID+"', '"+player.getTotalServe()+"', '"+
				player.getTotalServeError()+"', '"+player.getTotalServeAce()+"', '"+player.getTotalSet()+"', '"+
				player.getTotalAssist()+"', '"+player.getTotalBallHandlingError()+"', '"+player.getTotalPass0()+
				"', '"+player.getTotalPass1()+"', '"+player.getTotalPass2()+"', '"+player.getTotalPass3()+"', '"+
				player.getTotalAttackKill()+"', '"+player.getTotalAttackError()+"', '"+player.getTotalAttackAttempt()+
				"', '"+player.getTotalDig()+"', '"+player.getTotalBlock()+"', '"+player.getTotalBlockError()+"', '"+
				player.getTotalBlockAttempt()+"');";
		return query;
	}
	
	/**
	 * Sets the global variable gameID to whatever the gameID of the uploaded Game was.
	 * @param statistics The Game that was inserted
	 */
	private void setGameID(Game statistics){
		String query = "SELECT "+COL_GAME_ID+" FROM  `"+TABLE_GAME_INFO+"` " +
				"WHERE `"+COL_DESCRIPTION+"` = '"+statistics.getDescription()+"' AND `"+COL_DATE+
				"` = '"+statistics.getDate()+"' AND `"+COL_ROUND+"` = '"+statistics.getRound()+
				"' AND `"+COL_TYPE+"` = '"+statistics.getGameType()+"'";
		try {
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()){
				gameID = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * Runs an update on the database
	 * @param update The update to run
	 */
	private void runUpdate(String update){
    		try {
				Class.forName(DB_CLASS);
				stmt.executeUpdate(update);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException sql){
				// TODO Auto-generated catch block
				sql.printStackTrace();
			}
	}
}
